package com.thkj.modules.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.thkj.common.baseDao.BaseDao;
import com.thkj.modules.user.entity.DisinfectRecord;


public interface DisinfectRecordMapper extends BaseDao<DisinfectRecord> {

    /**
     * 统计一天的消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT sum(duration)" +
            "FROM  thkj_disinfect_record " +
            "WHERE DATE_FORMAT(craate_time,'%Y-%m-%d')= #{timeStr} " +
            "and device_id = #{device_id} " +
            "ORDER BY DATE(craate_time) DESC ")
    Double queryDaySumByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计年的消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT sum(duration)" +
            "FROM  thkj_disinfect_record " +
            "WHERE YEAR(craate_time)= #{timeStr} " +
            "and device_id = #{device_id} " +
            "ORDER BY DATE(craate_time) DESC ")
    Double queryYearSumByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计月的消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT sum(duration)" +
            "FROM  thkj_disinfect_record " +
            "WHERE DATE_FORMAT(craate_time,'%Y-%m')= #{timeStr} " +
            "and device_id = #{device_id} " +
            "ORDER BY DATE(craate_time) DESC ")
    Double queryMonthSumByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计一周的消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT sum(duration)" +
            "FROM  thkj_disinfect_record " +
            "WHERE YEARWEEK(date_format(craate_time,'%Y-%m-%d')) = YEARWEEK(now()) " +
            "and device_id = #{device_id} " +
            "ORDER BY DATE(craate_time) DESC ")
    Double queryWeekSumByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计一天的消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT  SUM(duration) " +
            " FROM thkj_disinfect_record " +
            "where device_id =#{device_id} " +
            "and DATE_FORMAT(craate_time,'%Y-%m-%d')= #{timeStr} " +
            "GROUP BY DATE(craate_time)  ")
    List<Double> querySumDayGroupByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);

    /**
     * 统计一年的每天消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT SUM(duration) " +
            "FROM thkj_disinfect_record " +
            "where device_id =#{device_id} " +
            "and YEAR(craate_time)= #{timeStr} " +
            "GROUP BY DATE(craate_time)  ")
    List<Double> querySumYearGroupByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计一月的每天消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT  SUM(duration) " +
            " FROM thkj_disinfect_record " +
            "where device_id =#{device_id} " +
            "and DATE_FORMAT(craate_time,'%Y-%m')= #{timeStr}  " +
            "GROUP BY DATE(craate_time)  ")
    List<Double> querySumMonthGroupByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);


    /**
     * 统计本周的每天消毒总时长
     *
     * @param
     * @return datediff(week, 时间字段,
     */
    @Select("SELECT SUM(duration) " +
            " FROM thkj_disinfect_record " +
            "where device_id =#{device_id} " +
            "and YEARWEEK(date_format(craate_time,'%Y-%m-%d')) = YEARWEEK(now())  " +
            "GROUP BY DATE(craate_time)  ")
    List<Double> querySumWeekGroupByWorkerId(@Param("device_id") String device_id, @Param("timeStr") String timeStr);

}

